﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class EmpBookBankDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public EmpBookBankDAL()
        {

        }
        public DataTable ListData(string emp_seq)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);

                strSQL = "SELECT ebb.book_seq, ebb.bank_acc_no, ebb.bank_acc_name"
                    + ", ebb.bank_seq, ebb.branch_seq, ebb.remarks, ebb.record_status"
                    + ", b.bank_id, b.bank_name, bb.branch_id, bb.branch_name"
                    + " FROM hrm_ps_emp_book_banks ebb"
                    + " LEFT JOIN fim_mas_banks b on ebb.bank_seq = b.bank_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb on ebb.branch_seq = bb.branch_seq"
                    + " WHERE ebb.record_status not like @DeleteRecord"
                    + (!string.IsNullOrEmpty(emp_seq) ? " AND ebb.emp_seq = @emp_seq" : "");
                dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dtb;
        }

        public void Insert(string emp_seq, string bank_acc_name, string bank_acc_no
            , string bank_seq, string branch_seq, string remarks
            , string record_status, string created_by)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@book_seq", "");
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@bank_acc_name", bank_acc_name);
                dbManager.AddParameters(3, "@bank_acc_no", bank_acc_no);
                dbManager.AddParameters(4, "@bank_seq", bank_seq);
                dbManager.AddParameters(5, "@branch_seq", branch_seq);
                dbManager.AddParameters(6, "@remarks", remarks);
                dbManager.AddParameters(7, "@record_status", record_status);
                dbManager.AddParameters(8, "@created_by", created_by);
                dbManager.AddParameters(9, "@created_when", DateTime.Today);

                strSQL = "INSERT INTO hrm_ps_emp_book_banks"
                    + "(emp_seq, bank_acc_name, bank_acc_no, bank_seq, branch_seq"
                        + ", record_status, created_by, created_when)"
                    + " VALUES(@emp_seq, @bank_acc_name, @bank_acc_no, @bank_seq, @branch_seq"
                        + ", @record_status, @created_by, @created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(string book_seq, string emp_seq, string bank_acc_name, string bank_acc_no
            , string bank_seq, string branch_seq, string remarks
            , string record_status, string updated_by)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@book_seq", book_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@bank_acc_name", bank_acc_name);
                dbManager.AddParameters(3, "@bank_acc_no", bank_acc_no);
                dbManager.AddParameters(4, "@bank_seq", bank_seq);
                dbManager.AddParameters(5, "@branch_seq", branch_seq);
                dbManager.AddParameters(6, "@remarks", remarks);
                dbManager.AddParameters(7, "@record_status", record_status);
                dbManager.AddParameters(8, "@updated_by", updated_by);
                dbManager.AddParameters(9, "@updated_when", DateTime.Today);

                strSQL = "UPDATE hrm_ps_emp_book_banks"
                        + " SET bank_acc_name = @bank_acc_name"
                        + ", bank_acc_no = @bank_acc_no"
                        + ", bank_seq = @bank_seq"
                        + ", branch_seq = @branch_seq"
                        + ", remarks = @remarks"
                        + ", updated_by = @updated_by"
                        + ", updated_when = @updated_when"
                    + " WHERE book_seq = @book_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(string book_seq, string empSeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@book_seq", book_seq);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", empSeq);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE hrm_ps_emp_book_banks"
                        + " SET RECORD_STATUS = @DeleteRecord"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE book_seq = @book_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

        }
    }
}
